package com.codingman.springmvcdemo6.crud;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.stereotype.Repository;
@Repository
public class EmployeeDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public Boolean save(Employee e) {
		String query = "insert into employee(name,password,email,sex,country) values (?,?,?,?,?)";

		return jdbcTemplate.execute(query, new PreparedStatementCallback<Boolean>() {
			@Override
			public Boolean doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setString(1, e.getName());
				ps.setString(2, e.getPassword());
				ps.setString(3, e.getEmail());
				ps.setString(4, e.getSex());
				ps.setString(5, e.getCountry());

				return ps.execute();
			}
		});
	}

	public int update(Employee e) {
		String query = "update employee set name=?,password=?,email=?,sex=?,country=? where id=?";
		return jdbcTemplate.update(query, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, e.getName());
				ps.setString(2, e.getPassword());
				ps.setString(3, e.getEmail());
				ps.setString(4, e.getSex());
				ps.setString(5, e.getCountry());
				ps.setInt(6, e.getId());
			}
		});
	}

	public int delete(int id) {
		String query = "delete from employee where id=?";
		return jdbcTemplate.update(query, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setInt(1, id);
			}
		});
	}

	public Employee getEmployeeById(int id) {
		String query = "select * from employee where id=?";
		return jdbcTemplate.queryForObject(query, new BeanPropertyRowMapper<Employee>(Employee.class), id);
	}

	public List<Employee> getAllEmployees() {
		String query = "select * from employee";
		return jdbcTemplate.query(query, new BeanPropertyRowMapper<Employee>(Employee.class));
	}
}
